#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔄 验证最终的实例表结构...\n');

try {
  // 获取最终的实例表统计
  const finalTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();

  console.log(`📊 最终实例表统计:`);
  console.log(`- 总实例表数: ${finalTables.length}`);
  
  // 验证是否都是标准格式
  const specialtyPrefixes = ['piping', 'electrical', 'hvac', 'communication', 'instrumentation'];
  const standardTables = finalTables.filter(table => {
    const tableName = table.name;
    return specialtyPrefixes.some(prefix => tableName.includes(`_${prefix}_`));
  });

  const nonStandardTables = finalTables.filter(table => {
    const tableName = table.name;
    return !specialtyPrefixes.some(prefix => tableName.includes(`_${prefix}_`));
  });

  console.log(`- 标准格式表数: ${standardTables.length}`);
  console.log(`- 非标准表数: ${nonStandardTables.length}`);

  if (nonStandardTables.length > 0) {
    console.log(`\n⚠️  剩余非标准表:`);
    nonStandardTables.forEach(table => {
      console.log(`  - ${table.name}`);
    });
  }

  // 按专业统计
  const specialtyStats = {};
  const specialtyMapping = {
    'piping': '配管专业',
    'electrical': '电气专业', 
    'hvac': '暖通专业',
    'communication': '通信专业',
    'instrumentation': '仪表专业'
  };

  standardTables.forEach(table => {
    const tableName = table.name;
    let specialty = '其他';
    
    Object.entries(specialtyMapping).forEach(([prefix, chineseSpecialty]) => {
      if (tableName.includes(`_${prefix}_`)) {
        specialty = chineseSpecialty;
      }
    });
    
    if (!specialtyStats[specialty]) {
      specialtyStats[specialty] = 0;
    }
    specialtyStats[specialty]++;
  });

  console.log(`\n🏢 按专业分布:`);
  Object.entries(specialtyStats).forEach(([specialty, count]) => {
    console.log(`  ${specialty}: ${count}个表`);
  });

  // 分析对象类覆盖情况
  console.log(`\n📋 对象类覆盖分析:`);
  
  const deviceClasses = db.prepare(`
    SELECT 
      dtm.classification_name,
      dtm.specialty,
      COUNT(DISTINCT d.device_id) as device_count
    FROM device_type_mappings dtm
    LEFT JOIN devices d ON dtm.classification_code = d.classification_code
    WHERE dtm.classification_name IS NOT NULL 
      AND dtm.classification_name != ''
      AND dtm.english_name IS NOT NULL
      AND dtm.english_name != ''
    GROUP BY dtm.classification_name, dtm.specialty
    ORDER BY dtm.specialty, dtm.classification_name
  `).all();

  console.log(`- devices表中的对象类: ${deviceClasses.length}个`);
  console.log(`- 标准实例表数: ${standardTables.length}个`);

  const totalDevicesInClasses = deviceClasses.reduce((sum, cls) => sum + cls.device_count, 0);
  console.log(`- 所有对象类包含的设备总数: ${totalDevicesInClasses}个`);

  console.log(`\n✅ 实例表重组成功完成!`);
  console.log(`🎯 现在有 ${standardTables.length} 个标准实例表，每个对应一个对象类`);
  console.log(`📈 优化效果: 从152个表减少到${finalTables.length}个表，清理了${152 - finalTables.length}个冗余表`);
  
  if (nonStandardTables.length === 0) {
    console.log(`🎉 所有实例表都已标准化为 device_instances_{specialty}_{english_name} 格式!`);
  }

} catch (error) {
  console.error('❌ 验证失败:', error.message);
} finally {
  db.close();
}